﻿Imports DevExpress.XtraEditors

Public Class frmOperation_Form
    Public id_operasi As Long

    Private data_tab_1 As New DataTable
    Private data_tab_2 As New DataTable
    Private data_tab_3 As New DataTable

    Private dbconfig() As Object

    Private Sub view_server(ByVal lookup As DevExpress.XtraEditors.LookUpEdit)
        Dim data As DataTable = mysql_query("SELECT id_host,nama_host,host,db FROM tb_host WHERE is_local = '1'")

        lookup.Properties.DataSource = data

        lookup.Properties.DisplayMember = "nama_host"
        lookup.Properties.ValueMember = "id_host"
    End Sub

    Private Sub view_list(ByVal indeks_operasi As Integer, ByVal listbox As DevExpress.XtraEditors.CheckedListBoxControl)
        Dim tar_host As String = ""
        Dim tar_port As String = "3306"
        Dim tar_username As String = ""
        Dim tar_password As String = ""
        Dim tar_database As String = ""
        Dim query As String = ""
        Dim display As String = ""

        If indeks_operasi = 1 Then
            tar_host = dbconfig(0).ToString
            tar_port = dbconfig(1).ToString
            tar_username = dbconfig(2).ToString
            tar_password = dbconfig(3).ToString
            tar_database = dbconfig(4).ToString

            query = "SHOW TABLES"
            display = "Tables_in_" & tar_database
        ElseIf indeks_operasi = 2 Then
            tar_host = dbconfig(0).ToString
            tar_port = dbconfig(1).ToString
            tar_username = dbconfig(2).ToString
            tar_password = dbconfig(3).ToString
            tar_database = dbconfig(4).ToString

            query = String.Format("SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'FUNCTION'", tar_database)
            display = "SPECIFIC_NAME"
        ElseIf indeks_operasi = 3 Then
            tar_host = dbconfig(0).ToString
            tar_port = dbconfig(1).ToString
            tar_username = dbconfig(2).ToString
            tar_password = dbconfig(3).ToString
            tar_database = dbconfig(4).ToString

            query = String.Format("SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE'", tar_database)
            display = "SPECIFIC_NAME"
        End If

        Dim data As DataTable = mysql_query(query, -1, False, tar_host, CType(tar_port, Long), tar_username, tar_password, tar_database)

        listbox.DataSource = data
        listbox.DisplayMember = display
        listbox.ValueMember = display
    End Sub

    Private Sub add_combo_grid(ByVal indeks_operasi As Integer, ByVal grid As DevExpress.XtraGrid.Views.Grid.GridView, ByVal col As Integer, ByVal tabel As DevExpress.XtraEditors.BaseCheckedListBoxControl.CheckedItemCollection)
        Dim combo As New DevExpress.XtraEditors.Repository.RepositoryItemComboBox
        combo.TextEditStyle = DevExpress.XtraEditors.Controls.TextEditStyles.DisableTextEditor
        combo.Items.Clear()

        For i As Integer = 0 To tabel.Count - 1
            Dim query As String = String.Format("DESC {0}", tabel.Item(i).ToString)
            Dim data As DataTable = mysql_query(query, -1, False, dbconfig(0), dbconfig(1), dbconfig(2), dbconfig(3), dbconfig(4))

            For j As Integer = 0 To data.Rows.Count - 1
                combo.Items.Add(tabel.Item(i).ToString & "." & data.Rows(j)(0).ToString)
            Next
        Next

        grid.Columns(col).ColumnEdit = combo
    End Sub

    Private Sub generate_format(container As DevExpress.XtraEditors.TextEdit, grid As DevExpress.XtraGrid.Views.Grid.GridView)
        Dim format As String = container.Text
        Dim num_parameter As Integer = grid.RowCount

        If num_parameter <> 0 Then
            format = format & get_separator()
        End If

        For i As Integer = 0 To num_parameter - 1

            If i <> 0 Then
                format = format & get_separator()
            End If

            format = format & GridViewSelectKeyParameter.GetRowCellValue(i, "name")
        Next

        MemoEditSelectFormat.Text = format
    End Sub

    Private Sub frmOperation_Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        XtraTabPageInsert.PageEnabled = True
        XtraTabPageUpdate.PageEnabled = True
        XtraTabPageDelete.PageEnabled = True
        XtraTabPageSelect.PageEnabled = True
        XtraTabPageProcedure.PageEnabled = True
        XtraTabPageFunction.PageEnabled = True

        LookUpEditSelectServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditSelectServer)

        LookUpEditDeleteServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditDeleteServer)

        LookUpEditInsertServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditInsertServer)

        LookUpEditUpdateServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditUpdateServer)

        LookUpEditFunctionServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditFunctionServer)

        LookUpEditProcedureServer.Properties.AppearanceDropDownHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center
        view_server(LookUpEditProcedureServer)

        Dim tmpData As DataTable
        data_tab_1.Clear()
        data_tab_2.Clear()
        data_tab_3.Clear()

        If id_operasi <> -1 Then
            XtraTabPageInsert.PageEnabled = False
            XtraTabPageUpdate.PageEnabled = False
            XtraTabPageDelete.PageEnabled = False
            XtraTabPageSelect.PageEnabled = False
            XtraTabPageProcedure.PageEnabled = False
            XtraTabPageFunction.PageEnabled = False

            Dim data As DataTable = mysql_query(String.Format("SELECT * FROM tb_operasi WHERE id_operasi = '{0}'", id_operasi))

            Dim jenis_operasi As String = data.Rows(0)("id_jenis_operasi").ToString
            Dim jenis_sql As String = data.Rows(0)("id_jenis_sql").ToString
            Dim nama_operasi As String = data.Rows(0)("nama_operasi").ToString
            Dim keyword As String = data.Rows(0)("keyword").ToString
            Dim is_publik As Boolean = data.Rows(0)("is_publik").ToString
            Dim id_host As String = data.Rows(0)("id_host").ToString


            If jenis_operasi.Equals("4") Then
                XtraTabPageSelect.PageEnabled = True
                XtraTabControlOperation.SelectedTabPageIndex = 0

                TextEditSelectName.Text = nama_operasi
                TextEditSelectKeyword.Text = keyword

                If is_publik = True Then
                    CheckEditSelectPublic.Checked = True
                Else
                    CheckEditSelectPublic.Checked = False
                End If

                LookUpEditSelectServer.EditValue = Nothing
                LookUpEditSelectServer.ItemIndex = LookUpEditSelectServer.Properties.GetDataSourceRowIndex("id_host", id_host)

                Dim data_tabel As DataTable = mysql_query(String.Format("SELECT * FROM tb_operasi_tabel WHERE id_operasi = '{0}'", id_operasi))

                For i As Integer = 0 To data_tabel.Rows.Count - 1
                    For j As Integer = 0 To CheckedListBoxControlSelectTable.ItemCount - 1
                        If CheckedListBoxControlSelectTable.GetItemText(j) = data_tabel.Rows(i)("nama_tabel").ToString Then
                            CheckedListBoxControlSelectTable.SetItemChecked(j, True)
                        End If
                    Next
                Next

                If data_tabel.Rows.Count <= 0 Then
                    CheckedListBoxControlSelectTable_ItemCheck(Nothing, Nothing)
                End If

                'Fill Output Grid
                tmpData = mysql_query(String.Format("SELECT output, nama_output FROM tb_operasi_output WHERE id_operasi = '{0}'", id_operasi))
                For i As Integer = 0 To tmpData.Rows.Count - 1
                    data_tab_1.Rows.Add(tmpData.Rows(i)("output").ToString, tmpData.Rows(i)("nama_output").ToString)
                Next

                ' Fill Conditional Grid
                tmpData = mysql_query(String.Format("SELECT parameter,nama_parameter,type FROM tb_operasi_parameter WHERE id_operasi = '{0}' AND is_kunci = '1'", id_operasi))
                For i As Integer = 0 To tmpData.Rows.Count - 1
                    data_tab_2.Rows.Add(tmpData.Rows(i)("parameter").ToString, tmpData.Rows(i)("nama_parameter").ToString, tmpData.Rows(i)("type").ToString)
                Next

                ' Fill Join Grid
                tmpData = mysql_query(String.Format("SELECT SPLIT(field_join,'=',1) as 'a',SPLIT(field_join,'=',2) as 'b' FROM tb_operasi_join WHERE id_operasi = '{0}'", id_operasi))
                For i As Integer = 0 To tmpData.Rows.Count - 1
                    data_tab_3.Rows.Add(tmpData.Rows(i)("a").ToString, tmpData.Rows(i)("b").ToString)
                Next
            End If
        Else
            XtraTabControlOperation.SelectedTabPageIndex = 0

            TextEditInsertName.Text = ""
            TextEditInsertKeyword.Text = ""
            CheckEditInsertPublic.Checked = False
            LookUpEditInsertServer.EditValue = Nothing
            CheckedListBoxControlInsertTable.DataSource = Nothing
            MemoEditInsertFormat.Text = ""
            GridControlInsertValueParameter.DataSource = Nothing
            DataNavigatorInsertValueParameter.DataSource = Nothing

            TextEditUpdateName.Text = ""
            TextEditUpdateKeyword.Text = ""
            CheckEditUpdatePublic.Checked = False
            LookUpEditUpdateServer.EditValue = Nothing
            CheckedListBoxControlUpdateTable.DataSource = Nothing
            MemoEditUpdateFormat.Text = ""
            GridControlUpdateValueParameter.DataSource = Nothing
            DataNavigatorUpdateValueParameter.DataSource = Nothing
            GridControlUpdateKeyParameter.DataSource = Nothing
            DataNavigatorUpdateKeyParameter.DataSource = Nothing


            TextEditDeleteName.Text = ""
            TextEditDeleteKeyword.Text = ""
            CheckEditDeletePublic.Checked = False
            LookUpEditDeleteServer.EditValue = Nothing
            CheckedListBoxControlDeleteTable.DataSource = Nothing
            MemoEditDeleteFormat.Text = ""
            GridControlDeleteKeyParameter.DataSource = Nothing
            DataNavigatorDeleteKeyParameter.DataSource = Nothing

            TextEditSelectName.Text = ""
            TextEditSelectKeyword.Text = ""
            CheckEditSelectPublic.Checked = False
            LookUpEditSelectServer.EditValue = Nothing
            CheckedListBoxControlSelectTable.DataSource = Nothing
            MemoEditSelectFormat.Text = ""
            GridControlSelectOutput.DataSource = Nothing
            DataNavigatorSelectOutput.DataSource = Nothing
            GridControlSelectKeyParameter.DataSource = Nothing
            DataNavigatorSelectKeyParameter.DataSource = Nothing
            GridControlSelectJoin.DataSource = Nothing
            DataNavigatorSelectJoin.DataSource = Nothing

            TextEditFunctionName.Text = ""
            TextEditFunctionKeyword.Text = ""
            CheckEditFunctionPublic.Checked = False
            LookUpEditFunctionServer.EditValue = Nothing
            CheckedListBoxControlFunctionFunction.DataSource = Nothing
            MemoEditFunctionFormat.Text = ""
            GridControlFunctionValueParameter.DataSource = Nothing

            TextEditProcedureName.Text = ""
            TextEditProcedureKeyword.Text = ""
            CheckEditProcedurePublic.Checked = False
            LookUpEditProcedureServer.EditValue = Nothing
            CheckedListBoxControlProcedureProcedure.DataSource = Nothing
            MemoEditProcedureFormat.Text = ""
            GridControlProcedureValueParameter.DataSource = Nothing
        End If
    End Sub

    ' ================== SELECT SECTION
    Private Sub LookUpEditSelectServer_EditValueChanged(sender As Object, e As EventArgs) Handles LookUpEditSelectServer.EditValueChanged
        Cursor = Cursors.WaitCursor

        If LookUpEditSelectServer.EditValue <> Nothing Then
            Dim data As DataTable = mysql_query(String.Format("SELECT * FROM tb_host WHERE id_host = '{0}'", LookUpEditSelectServer.EditValue.ToString))
            dbconfig = New Object() {data.Rows(0)("host"), data.Rows(0)("port"), data.Rows(0)("username"), data.Rows(0)("password"), data.Rows(0)("db")}
            view_list(1, CheckedListBoxControlSelectTable)
        End If

        Cursor = Cursors.Default
    End Sub

    Private Sub CheckedListBoxControlSelectTable_ItemCheck(sender As Object, e As Controls.ItemCheckEventArgs) Handles CheckedListBoxControlSelectTable.ItemCheck

        Cursor = Cursors.WaitCursor

        If data_tab_1.Columns.Count < 2 Then
            data_tab_1.Columns.Add("field")
            data_tab_1.Columns.Add("name")
        End If

        GridControlSelectOutput.DataSource = data_tab_1
        DataNavigatorSelectOutput.DataSource = data_tab_1

        If data_tab_2.Columns.Count < 3 Then
            data_tab_2.Columns.Add("field")
            data_tab_2.Columns.Add("name")
            data_tab_2.Columns.Add("type")
        End If

        GridControlSelectKeyParameter.DataSource = data_tab_2
        DataNavigatorSelectKeyParameter.DataSource = data_tab_2

        If data_tab_3.Columns.Count < 2 Then
            data_tab_3.Columns.Add("field1")
            data_tab_3.Columns.Add("field2")
        End If

        GridControlSelectJoin.DataSource = data_tab_3
        DataNavigatorSelectJoin.DataSource = data_tab_3

        Dim tabel As DevExpress.XtraEditors.BaseCheckedListBoxControl.CheckedItemCollection = CheckedListBoxControlSelectTable.CheckedItems
        add_combo_grid(4, GridViewSelectOutput, 0, tabel)
        add_combo_grid(4, GridViewSelectKeyParameter, 0, tabel)
        add_combo_grid(4, GridViewSelectJoin, 0, tabel)
        add_combo_grid(4, GridViewSelectJoin, 1, tabel)

        Cursor = Cursors.Default
    End Sub

    Private Sub TextEditSelectKeyword_EditValueChanged(sender As Object, e As EventArgs) Handles TextEditSelectKeyword.EditValueChanged
        generate_format(TextEditSelectKeyword, GridViewSelectKeyParameter)
    End Sub

    Private Sub GridViewSelectKeyParameter_RowCountChanged(sender As Object, e As EventArgs) Handles GridViewSelectKeyParameter.RowCountChanged
        generate_format(TextEditSelectKeyword, GridViewSelectKeyParameter)
    End Sub

    Private Sub GridViewSelectKeyParameter_ValidateRow(sender As Object, e As DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs) Handles GridViewSelectKeyParameter.ValidateRow
        generate_format(TextEditSelectKeyword, GridViewSelectKeyParameter)
    End Sub
    ' ================== END SELECT SECTION

    Private Sub SimpleButtonSave_Click(sender As Object, e As EventArgs) Handles SimpleButtonSave.Click
        Cursor = Cursors.WaitCursor

        If XtraTabControlOperation.SelectedTabPageIndex = 0 Then
            Dim nama_operasi As String = TextEditSelectName.Text
            Dim keyword As String = TextEditSelectKeyword.Text

            If Not isStrEmpty(New String() {nama_operasi, keyword}) Or GridViewSelectOutput.RowCount <= 0 Or GridViewSelectKeyParameter.RowCount <= 0 Or Not isGridFilled(GridViewSelectOutput) Or Not isGridFilled(GridViewSelectKeyParameter) Or Not isGridFilled(GridViewSelectJoin) Or isKeywordExists(id_operasi, keyword) Then
                XtraMessageBox.Show("Please fill all required field!", "Invalid input data", MessageBoxButtons.OK, MessageBoxIcon.Error)
                GoTo not_valid
            Else
                GoTo valid
            End If
        End If

valid:
        Try

            If id_operasi <> -1 Then ' If Edit Mode Selected, Remove the data first then re-Add it
                mysql_non_query(String.Format("DELETE FROM tb_operasi WHERE id_operasi = '{0}'", id_operasi))
            End If

            If XtraTabControlOperation.SelectedTabPageIndex = 0 Then ' Save If SELECT Operation Added/Edited
                Dim query As String = ""
                Dim num As Integer = 0
                Dim id_operasi As String = ""

                Dim nama_operasi As String = TextEditSelectName.Text
                Dim keyword As String = TextEditSelectKeyword.Text.ToUpper
                Dim is_publik As String = "0"

                If (CheckEditSelectPublic.Checked = True) Then
                    is_publik = "1"
                End If

                Dim id_host As String = LookUpEditSelectServer.EditValue.ToString
                Dim id_jenis_operasi As String = "4"
                Dim id_jenis_sql As String = "1"

                mysql_non_query(String.Format("INSERT INTO tb_operasi(id_host,id_jenis_operasi,id_jenis_sql,keyword,is_publik,nama_operasi) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')", id_host, id_jenis_operasi, id_jenis_sql, keyword, is_publik, nama_operasi))

                id_operasi = mysql_query("SELECT LAST_INSERT_ID()", 0)

                num = CheckedListBoxControlSelectTable.CheckedItems.Count

                For i As Integer = 0 To num - 1
                    mysql_non_query(String.Format("INSERT INTO tb_operasi_tabel(id_operasi,nama_tabel) VALUES('{0}','{1}')", id_operasi, CheckedListBoxControlSelectTable.CheckedItems.Item(i).ToString))
                Next

                num = GridViewSelectOutput.RowCount

                For i As Integer = 0 To num - 1
                    mysql_non_query(String.Format("INSERT INTO tb_operasi_output(id_operasi,output,nama_output) VALUES('{0}','{1}','{2}')", id_operasi, GridViewSelectOutput.GetRowCellValue(i, "field").ToString, GridViewSelectOutput.GetRowCellValue(i, "name").ToString))
                Next

                num = GridViewSelectKeyParameter.RowCount

                For i As Integer = 0 To num - 1
                    mysql_non_query(String.Format("INSERT INTO tb_operasi_parameter(id_operasi,parameter,nama_parameter,is_kunci,type) VALUES('{0}','{1}','{2}','{3}','{4}')", id_operasi, GridViewSelectKeyParameter.GetRowCellValue(i, "field").ToString, GridViewSelectKeyParameter.GetRowCellValue(i, "name").ToString, "1", GridViewSelectKeyParameter.GetRowCellValue(i, "type").ToString))
                Next

                num = GridViewSelectJoin.RowCount

                For i As Integer = 0 To num - 1
                    mysql_non_query(String.Format("INSERT INTO tb_operasi_join(id_operasi,field_join) VALUES('{0}','{1}')", id_operasi, GridViewSelectJoin.GetRowCellValue(i, "field1").ToString & " = " & GridViewSelectJoin.GetRowCellValue(i, "field2").ToString))
                Next
            End If
        Catch ex As Exception
            XtraMessageBox.Show("Can't save data to keyword database", "Connection failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Cursor = Cursors.Default

        Close()

        Cursor = Cursors.WaitCursor

        frmOperation.view_list()

not_valid:
        Cursor = Cursors.Default
    End Sub

End Class